﻿/******************************************************   
创建者: ljj
创建时间: 2018-01-03
修改:
修改时间: 
功能: 类别缺口计算
 ******************************************************/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library.Helper;
using george.Data;
using NHibernateVertica;
using InventoryToPredictAPI.model;
using com.nm.library;
using george.Helper;
using george.Web.Helper;

namespace InventoryToPredictAPI
{
    [Description("类别缺口计算")]
    public class GoodsKindGapCalcHandler:BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：ljj
        /// Create Date：2018-01-03
        [MustLogin]
        [Description("类别缺口计算信息")]
        [ReturnType(typeof(PaperData<GoodsKindGapCalcBaseItemModel.ShowModel>))]
        public ApiResult GetGoodsKindGapCalcBaseItemList([ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("大类")] string KindID = "", [ParamDescription("子项目")] string ItemOrSub = "",
            [ParamDescription("性别")] string SexID = "", [ParamDescription("周数")] int StartWeekNo = 1,
            [ParamDescription("周数")] int EndWeekNo = 13, [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                if (string.IsNullOrEmpty(companyID))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(extrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var kindIDStr = KindID.GetString();
                var itemOrSubStr = ItemOrSub.GetString();
                var count = ReturnGoodsKindGapCalcBaseItemCount(extrSource, companyID, ParentAreaID, kindIDStr, itemOrSubStr,
                    SexID);
                var result = PaperData<GoodsKindGapCalcBaseItemModel.ShowModel>.Create(count, Size, Page);
                var group = ReturnGoodsKindGapCalcBaseItemList(extrSource, companyID, ParentAreaID, kindIDStr, itemOrSubStr,
                    SexID, StartWeekNo, EndWeekNo, Page, Size).GroupBy(d => new
                {
                    d.ParentAreaID,
                    d.KindID,
                    d.KindName,
                    d.ItemOrSub,
                    d.SexID,
                    d.SexName,
                    d.AvgPrice,
                    d.PredictRate,
                    d.SKCQty,
                    d.ShopSKCDisplayQty,
                    d.AvgSaleQty,
                    d.MonthSaleRate,
                });
                var list = group.Select(d => new GoodsKindGapCalcBaseItemModel.ShowModel
                {
                    ParentAreaID = d.Key.ParentAreaID,
                    KindID = d.Key.KindID,
                    KindName = d.Key.KindName,
                    ItemOrSub = d.Key.ItemOrSub,
                    SexID = d.Key.SexID,
                    SexName = d.Key.SexName,
                    AvgPrice = d.Key.AvgPrice,
                    PredictRate = d.Key.PredictRate,
                    SKCQty = d.Key.SKCQty,
                    ShopSKCDisplayQty = d.Key.ShopSKCDisplayQty,
                    AvgSaleQty = d.Key.AvgSaleQty,
                    MonthSaleRate = d.Key.MonthSaleRate,
                    WeeklyItemList = d.Select(r => new GoodsKindGapCalcWeeklyItemModel.NeceModel
                    {
                        YearV = r.YearV,
                        WeekNo = r.WeekNo,
                        SaleTagAmount = r.SaleTagAmount,
                        SaleQty = r.SaleQty,
                        DisplayQty = r.DisplayQty,
                        DisplayTagAmount = r.DisplayTagAmount,
                        ValidSKCQty = r.ValidSKCQty,
                        SumValidSKCQty = r.SumValidSKCQty,
                        SumValidSKCAmount = r.SumValidSKCAmount,
                        HalfValidSKCQty = r.HalfValidSKCQty,
                        SumHalfValidSKCQty = r.SumHalfValidSKCQty,
                        SumHalfValidSKCAmount = r.SumHalfValidSKCAmount,
                        TotalValidSKCQty = r.TotalValidSKCQty,
                        SKCWidthGap = r.SKCWidthGap,
                        QtyGap = r.QtyGap,
                        AmountGap = r.AmountGap,
                        CSKCGap = r.CSKCGap,
                        CQtyGap = r.CQtyGap,
                        CAmountGap = r.CAmountGap,
                        OtherWidthGap = r.OtherWidthGap,
                        OtherQtyGap = r.OtherQtyGap,
                        OtherAmountGap = r.OtherAmountGap,
                    }).OrderBy(r => r.YearV).ThenBy(r => r.WeekNo).ToList(),
                }).OrderBy(d => d.ParentAreaID).ThenBy(d => d.KindID).ThenBy(d => d.ItemOrSub).ThenBy(d => d.SexID);
                result.Items = list.ToList();
                return Success(result);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败!");
            }
        }

        /// <summary>
        /// 返回类别缺口计算信息
        /// </summary>
        /// <param name="ParentAreaID"></param>
        /// <param name="KindID"></param>
        /// <param name="SubItem"></param>
        /// <param name="SexID"></param>
        /// <param name="StartWeekNo"></param>
        /// <param name="EndWeekNo"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2018-01-03
        public IList<GoodsKindGapCalcBaseItemModel.ListModel> ReturnGoodsKindGapCalcBaseItemList(string ExtrSource,
            string CompanyID, string ParentAreaID, string KindID, string ItemOrSub, string SexID, int StartWeekNo,
            int EndWeekNo, int Page, int Size)
        {
            try
            {
                var sql = $@"SELECT  table2.* ,
        weekly.YearV ,
        weekly.WeekNo ,
        weekly.SaleTagAmount ,
        weekly.SaleQty ,
        weekly.DisplayQty ,
        weekly.DisplayTagAmount ,
        weekly.ValidSKCQty ,
        weekly.SumValidSKCQty ,
        weekly.SumValidSKCAmount ,
        weekly.HalfValidSKCQty ,
        weekly.SumHalfValidSKCQty ,
        weekly.SumHalfValidSKCAmount ,
        weekly.TotalValidSKCQty ,
        weekly.SKCWidthGap ,
        weekly.QtyGap ,
        weekly.AmountGap ,
        weekly.CSKCGap ,
        weekly.CQtyGap ,
        weekly.CAmountGap ,
        weekly.OtherWidthGap ,
        weekly.OtherQtyGap ,
        weekly.OtherAmountGap
FROM    ( SELECT    table1.ExtrSource ,
                    table1.CompanyID ,
                    table1.ParentAreaID ,
                    table1.KindID ,
                    table1.ItemOrSub ,
                    table1.SexID ,
                    table1.AvgPrice ,
                    table1.PredictRate ,
                    table1.SKCQty ,
                    table1.ShopSKCDisplayQty ,
                    table1.AvgSaleQty ,
                    table1.MonthSaleRate ,
                    table1.EtlDate ,
                    table1.LoadTime ,
                    table1.SexName ,
                    table1.KindName
          FROM      ( SELECT    base.* ,
                                kind.KindName ,
                                sex.SexName ,
                                ROW_NUMBER() OVER ( ORDER BY base.ParentAreaID , base.YearV , base.WeekNo , base.KindID, base.ItemOrSub, base.SexID ) AS Row
                      FROM      ( SELECT    *
                                  FROM      ngoods.ngoods_KindGap_Calc_BaseItem
                                  WHERE     ExtrSource = '{ExtrSource}'
                                            AND CompanyID = '{CompanyID}'
                                            {
                        (string.IsNullOrEmpty(ParentAreaID) ? "" : $"AND ParentAreaID = '{ParentAreaID}'")
                    }
                                            {(string.IsNullOrEmpty(KindID) ? "" : $"AND KindID in {KindID}")}
                                            {(string.IsNullOrEmpty(ItemOrSub) ? "" : $"AND ItemOrSub in {ItemOrSub}")}
                                            {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
											AND YearV = YEAR(CURRENT_DATE)
                                            AND WeekNo = ( SELECT
                                                              Weekv
                                                           FROM
                                                              ndw.ndw_CalendarDays
                                                           WHERE
                                                              GregorianDay::date = CURRENT_DATE
                                                              AND ExtrSource = '{ExtrSource}'
                                                              AND CompanyID = '{CompanyID}'
                                                         )
                                ) base
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Sex
                                             WHERE  ExtrSource = '{ExtrSource}'
                                                    {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                                           ) sex ON base.ExtrSource = sex.ExtrSource
                                                    AND base.SexID = sex.SexID
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Kind
                                             WHERE  ExtrSource = '{ExtrSource}'
                                                    {(string.IsNullOrEmpty(KindID) ? "" : $"AND KindID in {KindID}")}
                                           ) kind ON base.ExtrSource = kind.ExtrSource
                                                     AND base.KindID = kind.KindID
                      GROUP BY  base.ExtrSource ,
                                base.CompanyID ,
                                base.YearV ,
                                base.WeekNo ,
                                base.ParentAreaID ,
                                base.KindID ,
                                base.ItemOrSub ,
                                base.SexID ,
                                base.AvgPrice ,
                                base.PredictRate ,
                                base.SKCQty ,
                                base.ShopSKCDisplayQty ,
                                base.AvgSaleQty ,
                                base.MonthSaleRate ,
                                base.EtlDate ,
                                base.LoadTime ,
                                sex.SexName ,
                                kind.KindName
                    ) table1
          WHERE     Row BETWEEN ( {Page} - 1 ) * {Size} + 1 AND {Page}
                    * {Size}
        ) table2
        LEFT JOIN ( SELECT  table1.*
                    FROM    ( SELECT    *
                              FROM      ngoods.ngoods_KindGap_Calc_WeeklyItem
                              WHERE     ExtrSource = '{ExtrSource}'
                                        AND CompanyID = '{CompanyID}'
                                        {(string.IsNullOrEmpty(ParentAreaID) ? "" : $"AND ParentAreaID = '{ParentAreaID}'")}
                                        {(string.IsNullOrEmpty(KindID) ? "" : $"AND KindID in {KindID}")}
                                        {(string.IsNullOrEmpty(ItemOrSub) ? "" : $"AND ItemOrSub in {ItemOrSub}")}
                                        {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
          
                            ) table1
                            INNER JOIN ( SELECT ExtrSource ,
                                                CompanyID ,
                                                YearV ,
                                                Weekv
                                         FROM   ndw.ndw_CalendarDays
                                         WHERE  GregorianDay::date >= ( CURRENT_DATE + ( 7 * {StartWeekNo} ) )
                                                AND GregorianDay::date <= ( CURRENT_DATE + ( 7 * {EndWeekNo} ) )
                                                AND ExtrSource = '{ExtrSource}'
                                                AND CompanyID = '{CompanyID}'
                                         GROUP BY ExtrSource ,
                                                CompanyID ,
                                                YearV ,
                                                Weekv
                                       ) table2 ON table1.ExtrSource = table2.ExtrSource
                                                   AND table1.CompanyID = table2.CompanyID
                                                   AND table1.YearV = table2.YearV
                                                   AND table1.WeekNo = table2.Weekv
                  ) weekly ON table2.ExtrSource = weekly.ExtrSource
                              AND table2.CompanyID = weekly.CompanyID
                              AND table2.ParentAreaID = weekly.ParentAreaID
                              AND table2.KindID = weekly.KindID
                              AND table2.ItemOrSub = weekly.ItemOrSub
                              AND table2.SexID = weekly.SexID";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<GoodsKindGapCalcBaseItemModel.ListModel>.ConvertToModel(dt);
                return list;
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }

        /// <summary>
        /// 返回类别缺口计算信息条数
        /// </summary>
        /// <param name="ParentAreaID"></param>
        /// <param name="KindID"></param>
        /// <param name="SubItem"></param>
        /// <param name="SexID"></param>
        /// <param name="StartWeekNo"></param>
        /// <param name="EndWeekNo"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2018-01-03
        public int ReturnGoodsKindGapCalcBaseItemCount(string ExtrSource, string CompanyID, string ParentAreaID,
            string KindID, string ItemOrSub, string SexID)
        {
            try
            {
                var sql = $@"SELECT    Count(1)
          FROM      ( SELECT    base.* ,
                                kind.KindName ,
                                sex.SexName ,
                                ROW_NUMBER() OVER ( ORDER BY base.ParentAreaID , base.YearV , base.WeekNo , base.KindID, base.ItemOrSub, base.SexID ) AS Row
                      FROM      ( SELECT    *
                                  FROM      ngoods.ngoods_KindGap_Calc_BaseItem
                                  WHERE     ExtrSource = '{ExtrSource}'
                                            AND CompanyID = '{CompanyID}'
                                            {
                        (string.IsNullOrEmpty(ParentAreaID) ? "" : $"AND ParentAreaID = '{ParentAreaID}'")
                    }
                                            {(string.IsNullOrEmpty(KindID) ? "" : $"AND KindID in {KindID}")}
                                            {(string.IsNullOrEmpty(ItemOrSub) ? "" : $"AND ItemOrSub in {ItemOrSub}")}
                                            {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
											AND YearV = YEAR(CURRENT_DATE)
                                            AND WeekNo = ( SELECT
                                                              Weekv
                                                           FROM
                                                              ndw.ndw_CalendarDays
                                                           WHERE
                                                              GregorianDay::date = CURRENT_DATE
                                                              AND ExtrSource = '{ExtrSource}'
                                                              AND CompanyID = '{CompanyID}'
                                                         )
                                ) base
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Sex
                                             WHERE  ExtrSource = '{ExtrSource}'
                                                    {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                                           ) sex ON base.ExtrSource = sex.ExtrSource
                                                    AND base.SexID = sex.SexID
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Kind
                                             WHERE  ExtrSource = '{ExtrSource}'
                                                    {(string.IsNullOrEmpty(KindID) ? "" : $"AND KindID in {KindID}")}
                                           ) kind ON base.ExtrSource = kind.ExtrSource
                                                     AND base.KindID = kind.KindID
                      GROUP BY  base.ExtrSource ,
                                base.CompanyID ,
                                base.YearV ,
                                base.WeekNo ,
                                base.ParentAreaID ,
                                base.KindID ,
                                base.ItemOrSub ,
                                base.SexID ,
                                base.AvgPrice ,
                                base.PredictRate ,
                                base.SKCQty ,
                                base.ShopSKCDisplayQty ,
                                base.AvgSaleQty ,
                                base.MonthSaleRate ,
                                base.EtlDate ,
                                base.LoadTime ,
                                sex.SexName ,
                                kind.KindName
                    ) table1";
                var cmd = Helper.GetSqlStringCommand(sql);
                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                return count;
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }


        /// Writer：ljj
        /// Create Date：2018-02-23
        [MustLogin]
        [Description("计算类别缺口")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult CalGoodsKindGapCalc()
        {
            try
            {
                var dateTime = DateTime.Now.ToString("yyyy-MM-dd");
                var server = IpAddressHelper.WebConfig("python-server"); //"192.168.10.127";
                var username = IpAddressHelper.WebConfig("python-server-user"); //"bi";
                var port = Convert.ToInt32(IpAddressHelper.WebConfig("python-server-port")); //"22";
                var password = IpAddressHelper.WebConfig("python-server-pass"); //"Km_etl2017";
                var cmdJudge = $"python /home/bi/scripts/python/etl/ndw/SP_ngoods_KindGap_Calc_Monitor.py";
                var cmd = $"python /home/bi/scripts/python/etl/ndw/SP_ngoods_KindGap_Calc.py {dateTime}";
                var resJudge = ConnLinux(server, username, port, password, cmdJudge);
                if (resJudge.Contains("1")) return Error("当前有用户正在计算!");
                var result = ConnLinux(server, username, port, password, cmd);
                return result.Contains("success") ? Success(0) : Error(result);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("计算失败!");
            }
        }
    }
}
